package com.wms_backend.dao;


import com.wms_backend.entity.Inbound;
import com.wms_backend.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.*;

@Repository
public interface InventoryDao extends JpaRepository<Inbound, Integer> {
    //as 关键字为每个字段指定键。
    @Query("SELECT i.name as name ,i.kind as kind,i.createTime as createTime ,i.createUser as createUser,i.unit as unit ,i.remainNumber as remainNumber ,i.warehouse as warehouse  FROM  Inbound i " +

            "   where LOWER(i.name) like %:name% and lower(i.kind)  like %:kind% " +
            "   and i.createUser in(select u.userId from User u where u.companyId=:company)  " +
            " and (:unit is null or i.unit=:unit) " +
            " and (:warehouse ='' or i.warehouse=:warehouse ) " +
            " and LOWER(i.createUser) like %:createUser%  " +
            " and (:number is null or i.remainNumber=:number) ")
    Page<List<Map<String, Object>>> findBySixParam(
            String name,
            String kind,
            Integer unit,
            Integer number,
            String warehouse,
            Integer company,
            String createUser,
            Pageable pageable
    );

    @Query("select s.shelfOrder as shelfOrder, s.status as status from Shelf s where s.repositoryName=:repositoryName ")
    List<Map<String, Object>> getRepositoryStatus(String repositoryName);

    @Query("select f.status as status ,f.floorOrder as floorOrder,  i.name as name ,i.companyId as companyId from Floor f left outer join Inbound i on f.inboundId=i.id  "+

            "where f.shelfId=(select s.id as id from Shelf s where s.repositoryName=:repositoryName "+
            "and s.shelfOrder=:shelfOrder ) ")
    List<Map<String, Object>> getShelfGoods(String repositoryName,Integer shelfOrder);


};
